import qrcode
import pathlib
from pyzbar.pyzbar import decode
import cv2 as cv
import numpy as np
import requests
import json
import sqlite3
import logging
import os
from openpyxl import Workbook

db=None
cur=None
def getID(text):
    data=text.split("data_source=\"contral_")
    list=[]
    for i in data :
        list.append(i[0:34])
    del list[0]
    return list

def existID(id):
    global db
    global cur    
    sql="select id from log where id='{id}'".format(id=id)
    r=executeSql(sql)
    if(r.fetchone()==None):
        return False
    else:
        return True

def executeSql(sql):
    global db
    global cur
    try:
        cur.execute(sql)
    except :
        logging.error(sql)
        db.rollback()
    else:
        db.commit()
        return cur

def initdb():
    global db
    global cur
    db = sqlite3.connect("log.db", check_same_thread=False)
    cur = db.cursor()
    cur.execute("select name from sqlite_master where type='table' and name='log' order by name")
    if(len(cur.fetchall())==0):
        sql=r'CREATE TABLE log (  "id" text NOT NULL,   "name" TEXT,   "class" TEXT,   "cardid" text,   "note" TEXT,   "time" TEXT,   PRIMARY KEY ("id") );'
        executeSql(sql)
    
initdb()

for root, dirs, files in os.walk(pathlib.Path.cwd()):
    for name in files:
        path=pathlib.Path(name)
        if(path.suffix==".png" or path.suffix==".jpg"):
            result = decode(cv.imread(str(path)))
            text=result[0][0].decode("utf-8")
            id=text[59:]
            if(not existID(id)):
                url=r"https://f.10086.cn/wxqyh/open/openFormAction!detailGetFroms.action?id="+id
                r=requests.get(url)
                js=json.loads(r.text)
                list=getID(js['data']['definitions'])
                info=[]
                for i in list:
                    try:
                        info.append(js['data']['itemMap'][i])
                        print(js['data']['itemMap'][i])
                    except:
                        info.append("")
                print(url)
                sql=r"INSERT INTO log(id, name, class, cardid, note, time ) VALUES ('{id}', '{name}', '{clas}', '{cardid}', '{note}', '{time}')".format(
                    id=id,
                    name=info[0],
                    clas=info[2],
                    cardid=info[3],
                    note=info[11],
                    time=js['data']['detail']['createaTime']
                )
                executeSql(sql)
            else:
                sql=r"select id , name from log where id='{id}'".format(id=id)
                r=executeSql(sql)
                print(r.fetchone())

wb = Workbook()








